package group.program.Service.impl;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.google.gson.Gson;
import group.program.Service.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import static java.sql.JDBCType.NULL;

@Service
public class TextAnalysisServiceImpl implements TextAnalysisService {
    private Logger logger = LoggerFactory.getLogger(getClass());
    @Autowired
    private NlpService nlpService;
    @Autowired
    private SqlService sqlService;
    private Boolean ifSql;
    @Override
    public String textAnalysis(HttpServletResponse response, String question) {
        //先提取信息
        List<LinkedHashMap<String,Object>>Data=getData(response,question);
        //没有查询到数据则直接返回大模型结果
        if(Data == null || Data.isEmpty()){
            if(ifSql){
                return "抱歉，知识库中没有查询到数据。";
            }
            return nlpService.getNlpApi(response,question);
        }
        if(question.contains("分布图")){
            nlpService.getNlpApi(response,"请在后面2次对话中重复生成上次对话的sql");
            return "请问您需要的是折线图，柱状图还是饼图呢？";
        }
        Gson gson = new Gson();
        String jsonString = gson.toJson(Data);
        // 返回并打印 JSON 字符串
        System.out.println(jsonString);
        return jsonString;
    }
    List<LinkedHashMap<String,Object>> getData(HttpServletResponse response, String question){
        //调用大语言模型提取数据
        String sql=nlpService.getNlpApi(response,question);
        // 定义正则表达式
        String regex = "```sql\\n([\\s\\S]*?)\\n```\n";
        Pattern pattern = Pattern.compile(regex);
        Matcher matcher = pattern.matcher(sql);
        // 提取匹配的部分
        String newSql;
        if (matcher.find()) {
            ifSql=true;
            newSql = matcher.group(1);
        }else{
            ifSql=false;
            return null;
        }
        String filteredSql = filterChinese(newSql);
        System.out.println(filteredSql);
        return sqlService.getData(filteredSql);
    }
    public String filterChinese(String sql) {
        // 定义匹配中文字符的正则表达式
        String regex = "[\\u4e00-\\u9fa5]+";
        Pattern pattern = Pattern.compile(regex);
        Matcher matcher = pattern.matcher(sql);
        // 替换中文部分为空字符串
        String newSql = matcher.replaceAll("");
        return newSql;
    }
//    void getExcel(HttpServletResponse response, List<LinkedHashMap<String, Object>> data) {
//        String fileName = "excel.xlsx";
//        response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");
//        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
//        response.setCharacterEncoding("utf-8");
//        List<List<Object>> excelData = new ArrayList<>();
//        if (!data.isEmpty()) {
//            List<String> headers = new ArrayList<>(data.get(0).keySet());
//            excelData.add(new ArrayList<>(headers));
//            for (LinkedHashMap<String, Object> rowMap : data) {
//                List<Object> row = new ArrayList<>();
//                for (String header : headers) {
//                    row.add(rowMap.get(header));
//                }
//                excelData.add(row);
//            }
//        }
//        try (OutputStream outputStream = response.getOutputStream()) {
//            ExcelWriter excelWriter = EasyExcel.write(outputStream).build();
//            WriteSheet writeSheet = EasyExcel.writerSheet("数据").build();
//            excelWriter.write(excelData, writeSheet);
//            excelWriter.finish();
//            outputStream.flush();
//        } catch (Exception e) {
//            logger.error("Error while exporting Excel file.", e);
//        }
//    }
}
